﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseAccess
{
    public class DBAccess : IDisposable
    {
        private string strConnString;
        private SqlConnection sqlConn;
        private SqlCommand sqlCommand;
        private string strLastError;
        //private string dbconn;

        #region (属性)
        public bool HandleErrors { get; set; }
        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        public SqlCommand Command
        {
            get
            {
                return sqlCommand;
            }
        }
        #endregion

        #region (方法)
        /// <summary>
        /// 构造方法，使用连接字符串构造
        /// </summary>
        /// <param name="connectionStr">
        /// </param>
        public DBAccess(string connectionStr = "server=localhost;database=liguo;uid=sa;pwd=sql")
        {

            strConnString = connectionStr;
            sqlConn = new SqlConnection(strConnString);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConn;
        }

        /// <summary>
        /// 添加带参查询的参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        public void AddParameter(string name, object value)
        {
            SqlParameter p = new SqlParameter(name, value);
            sqlCommand.Parameters.Add(p);
            return;
        }

        /// <summary>
        /// 添加带参查询的参数
        /// </summary>
        /// <param name="parameter"></param>
        public void AddParameter(SqlParameter parameter)
        {
            sqlCommand.Parameters.Add(parameter);
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (sqlConn.State == System.Data.ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            sqlCommand.Transaction = sqlConn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            sqlCommand.Transaction.Commit();
            sqlConn.Close();
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollbackTransaction()
        {
            sqlCommand.Transaction.Rollback();
            sqlConn.Close();
        }

        /// <summary>
        /// 执行不需要返回DataSet的查询
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">命令类型，默认为CommandType.Text</param>
        /// <param name="closeOnExit">执行完毕后是否关闭连接，默认为true</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQuery(string query, CommandType commandType = CommandType.Text, bool closeOnExit = true)
        {
            sqlCommand.CommandText = query;
            sqlCommand.CommandType = commandType;
            int cnt = -1;
            try
            {
                if (sqlConn.State == System.Data.ConnectionState.Closed)
                {
                    sqlConn.Open();
                }
                cnt = sqlCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                sqlCommand.Parameters.Clear();
                if (closeOnExit)
                {
                    sqlConn.Close();
                }
            }

            return cnt;
        }

        /// <summary>
        /// 执行查询并返回结果集上的一个SqlDataReader对象
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">命令类型，默认为CommandType.Text</param>
        /// <param name="closeOnExit">执行完毕后是否关闭连接，默认为true</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string query, CommandType commandType = CommandType.Text, bool closeOnExit = true)
        {
            sqlCommand.CommandText = query;
            sqlCommand.CommandType = commandType;

            SqlDataReader reader = null;
            try
            {
                if (sqlConn.State == System.Data.ConnectionState.Closed)
                {
                    sqlConn.Open();
                }
                if (closeOnExit)
                {
                    reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = sqlCommand.ExecuteReader();
                }
            }
            catch (System.Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                sqlCommand.Parameters.Clear();
            }

            return reader;
        }

        /// <summary>
        /// 执行查询并返回结果集
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandType">命令类型，默认为CommandType.Text</param>
        /// <param name="closeOnExit">执行完毕后是否关闭连接，默认为true</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string query, CommandType commandType = CommandType.Text, bool closeOnExit = true)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            sqlCommand.CommandText = query;
            sqlCommand.CommandType = commandType;

            adapter.SelectCommand = sqlCommand;

            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (System.Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                sqlCommand.Parameters.Clear();
                if (closeOnExit && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }

            return ds;
        }

        /// <summary>
        /// 执行查询并返回结果集的第一行的第一列，所有其他的行和列将被忽略。
        /// 如果找不到结果集中第一行的第一列,则返回 null。
        /// 如果数据库中的该值为 null，此查询将返回DBNull.Value。
        /// 一个典型的例子如下：
        /// <code>
        /// cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
        /// Int32 count = (Int32) cmd.ExecuteScalar();
        /// </code>
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <param name="commandtype">命令类型，默认为CommandType.Text</param>
        /// <param name="closeOnExit">执行完毕后是否关闭连接，默认为true</param>
        /// <returns></returns>
        public object ExecuteScalar(string query, CommandType commandtype = CommandType.Text, bool closeOnExit = true)
        {
            sqlCommand.CommandText = query;
            sqlCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (sqlConn.State == System.Data.ConnectionState.Closed)
                {
                    sqlConn.Open();
                }
                o = sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                sqlCommand.Parameters.Clear();
                if (closeOnExit)
                {
                    sqlConn.Close();
                }
            }

            return o;
        }

        public void Dispose()
        {
            sqlConn.Close();
            sqlConn.Dispose();
            sqlCommand.Dispose();
        }
        public int MedicalShopIcrease(String a, String b, String c, String d, int n, String e, String f, String g, int m, String h, String i, int p, String k, String l, String m1, int r, int s, int t, int u, int v)
        {
            int count = 0;
            string strSqll = "insert into DrugStoreManagement(StoreName,StorePormitID,StoreLisenceID,StoreRepresent,FormValue,PosterCode,RelatedPeople,StoreTelephone,BankNum,BankNumber,BankStoreName,Area,Address,content,openion,OtherPsNum,PriPsNum,MidPsNum,HighPsNum,SalePsNum)values('" + a + "','" + b + "','" + c + "','" + d + "'," + n + ",'" + e + "','" + f + "','" + g + "'," + m + ",'" + h + "','" + i + "'," + p + ",'" + k + "','" + l + "','" + m1 + "'," + r + "," + s + "," + t + "," + u + "," + v + ")";


            sqlConn.Open();

            SqlCommand sqlcomm = new SqlCommand(strSqll, sqlConn);
            count = sqlcomm.ExecuteNonQuery();
            sqlConn.Close();
            return count;
        }
        public int MedicalShopUpdate(int ID, String a, String b, String c, String d, int n, String e, String f, String g, int m, String h, String i, int p, String k, String l, String m1, int r, int s, int t, int u, int v)
        {
            int count = 0;
            string strSqll = "update DrugStoreManagement set StoreName='" + a + "',StorePormitID='" + b + "',StoreLisenceID='" + c + "',StoreRepresent='" + d + "',FormValue=" + n + ",PosterCode='" + e + "',RelatedPeople='" + f + "',StoreTelephone='" + g + "',BankNum=" + m + ",BankNumber=" + h + ",BankStoreName='" + i + "',Area=" + p + ",Address='" + k + "',content='" + l + "',openion='" + m1 + "',OtherPsNum=" + r + ",PriPsNum=" + s + ",MidPsNum=" + t + ",HighPsNum=" + u + ",SalePsNum=" + v + " where storeID=" + ID + "";


            sqlConn.Open();

            SqlCommand sqlcomm = new SqlCommand(strSqll, sqlConn);
            count = sqlcomm.ExecuteNonQuery();
            sqlConn.Close();
            return count;
        }
        #endregion

        #region (私有方法)
        private void HandleExceptions(Exception ex)
        {
            if (HandleErrors)
            {
                strLastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }
        #endregion
    }
}
